<?php

class mProduct extends CI_Model {
    
        ########################################################################
        ####### DATA PRODUCT ##############
        ########################################################################
        function getList()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                //$query = $dblokal->query("SELECT DISTINCT imap.item_code as mvalue, CONCAT_WS(' | ', imap.item_code, itm.item_name) as mdisplay
                  //                      FROM t_mtr_item_mapping imap	
                    //                    JOIN t_mtr_item itm on itm.item_code = imap.item_code
                      //                  ORDER BY mdisplay ASC");
$query = $dblokal->query("
                SELECT imap.item_code as mvalue, CONCAT_WS(' | ', imap.item_code, imap.item_name) as mdisplay
FROM t_mtr_item_mapping imap
ORDER BY mdisplay ASC");
                /*$query = $dblokal->query("SELECT DISTINCT imap.item_code as mvalue, itm.item_name as mdisplay
                                        FROM t_mtr_item_mapping imap	
                                        JOIN t_mtr_item itm on itm.item_code = imap.item_code
                                        ORDER BY mdisplay ASC");*/
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
    
      function getProductById($id)
          {    
                  $dblokal  = $this->load->database("default", TRUE);
                  $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name
                                           FROM t_mtr_item,t_mtr_item_group
                                           WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id
                                           AND item_code='$id' ");
                  if( $query->num_rows() > 0 ) {
                         return $query->result();
                } else {
                        return array();
                 }
 }
 
 
     function getProductById2($id)
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $query = $dblokal->query("SELECT t_mtr_item_mapping.item_code, t_mtr_item_group.item_group_name,t_mtr_item_mapping.item_name, t_mtr_item_mapping.default_price
            FROM t_mtr_item_mapping inner join t_mtr_item_group ON t_mtr_item_mapping.item_group_id=t_mtr_item_group.item_group_id where t_mtr_item_mapping.item_code ='$id'
                    
                 ");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function get($tgl="")
        {    
                $dblokal  = $this->load->database("default", TRUE);
                if($tgl == "") $tgl = date('Y-m-d');
                $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name
                                         FROM t_mtr_item,t_mtr_item_group
                                         WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id
                                         AND shipment_date='". $tgl ."' AND reseller_price > 0");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function gets()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name, v_combo_sales_affected.member_display as sales
                                         FROM t_mtr_item,t_mtr_item_group,v_combo_sales_affected
                                         WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id and v_combo_sales_affected.member_value = 
                                         AND reseller_price >= 0");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function getTerritoryName()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $userlogin = $this->session->userdata('username');
                //$query = $dblokal->query("select territory_name from t_mtr_territory where territory_id = f_get_cluster('$userlogin');");
                
                $query = $dblokal->query("select f_get_territory_path(f_get_cluster('$userlogin')) as territory_name");
                if( $query->num_rows() > 0 ) {
                        foreach($query->result() as $r){
                                return $r->territory_name;
                        }
                } else {
                        return "";
                }
	}
        
        function updateProduct(){
                #$dblokal  = $this->load->database("default", TRUE);
                #$dblokal->query("UPDATE t_mtr_item SET reseller_price='". $harga ."' WHERE item_code='". $id ."' AND shipment_date='". date("Y-m-d") ."'");
                
                $dblokal  = $this->load->database("default", TRUE);
                
                $territory_id           = "";
                $item_code              = $this->input->post('item_code');
                $pricing_date           = $this->input->post('pricing_date');
                $retail_price           = $this->input->post('retail_price');
                $condition_type         = $this->input->post('conditional_type');
                $condition_value        = $this->input->post('conditional_value');
                $sales_affected         = $this->input->post('sales_affected');
                
                $data = array("territory_id"=>$territory_id,
                              "item_code"=>$item_code,
                              "pricing_date"=>$pricing_date,
                              "retail_price"=>$retail_price,
                              "condition_type"=>$condition_type,
                              "condition_value"=>$condition_value,
                              "sales_affected"=>$sales_affected,
                              "created_by"=>$this->session->userdata('username'));
                
                $dblokal->insert("t_trx_pricing",$data);
        }
        
        function savePricing(){
                $dblokal  = $this->load->database("default", TRUE);
                
                $item_code      = $this->input->post("item_code");
				$pricing_date   = $this->input->post("pricing_date");
				$retail_price   = $this->input->post("retail_price");
				$condition_type = $this->input->post("condition_type");
				$condition_value= $this->input->post("condition_value");
				$sales_affected = $this->input->post("sales_affected");
                $selling_type= $this->input->post("selling_type");
				
				$terid = $this->mglobal->showdata("territory_id","t_mtr_territory",array('user_id'=>$this->session->userdata('username')),"dblokal");
                $data = array("territory_id"=>$terid,
                              "item_code"=>$item_code,
                              "pricing_date"=>$pricing_date,
                              "retail_price"=>$retail_price,
                              "condition_type"=>$condition_type,
                              "condition_value"=>$condition_value,
                              "sales_affected"=>$sales_affected,
							  "selling_type"=>$selling_type,
                              "created_by"=>$this->session->userdata('username'));
                
                $dblokal->insert("t_trx_pricing",$data);
        }
        
        function getGroup($itemCode){
                $dblokal  = $this->load->database("default", TRUE);
		$query = $dblokal->query("select f_get_item_group('$itemCode') as group_name");
                foreach($query->result() as $r){
                        return $r->group_name;
                }
	}
        
        function getPrice($itemCode){
                $dblokal  = $this->load->database("default", TRUE);
		$query = $dblokal->query("select f_get_price_retail('$itemCode') as price");
                foreach($query->result() as $r){
                        return $r->price;
                }
	}
        
        function getSalesAffected(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_sales_affected;");
                return $query->result();
		}
        
        function getConditionType(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_condition_type;");
                return $query->result();
		}
		
		function getSellingType(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_agent_selling_type;");
                return $query->result();
		}
        
        function getPricesData(){
                $this->load->library('serversidedatatables');
                $dblokal  = $this->load->database("default", TRUE);

                $sIndexColumn = "item_name";
                $status="";
                $defaultOrder = "1";
                $userlogin = $this->session->userdata('username');

                $aColumns = array("item_name", "item_code", "user_group_name", "pricing_date", "retail_price", "condition_type", "condition_value", "selling_type");
                // $aColumns = array("item_name", "item_code", "user_group_name", "pricing_date", "retail_price");

                $str1 = array(" , ", ", ' '");
                $str2 = array(" ", " ");
                $inputQuery = "select ".str_replace($str1, $str2, implode(", ", $aColumns))." from v_ssp_price where territory_id=f_get_cluster('".$userlogin."')";
                
                return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $status, $userlogin, $inputQuery, $aColumns, $sIndexColumn);
				// $query = $dblokal->query("select  imap.item_name, imap.item_code, price.pricing_date, 
    //                                         price.retail_price,price.condition_type,price.condition_value,
    //                                         price.selling_type, grp.user_group_name from t_trx_pricing price inner join 
    //                                         t_mtr_item_mapping imap on price.item_code=imap.item_code inner join t_mtr_user_group grp 
    //                                         on grp.user_group_id=price.sales_affected  
    //                                         on price.territory_id=f_get_cluster('".$this->session->userdata('username')."')");
    //             return $query->result();
		}
        
        //SSP 
        //edited : by alfin akhret
       	function getProductsData(){
       	        $this->load->library('serversidedatatables');
                $dblokal  = $this->load->database("default", TRUE);

                $sIndexColumn = "item_name";
                $status="";
                $defaultOrder = "1";
                $userlogin = $this->session->userdata('username');

                $aColumns = array("item_group_name", "iccid", "mdn", "item_name", "item_code", "original_distributor_code", "original_ship_to_code", "shipment_date", "istatus");
                $str1 = array(" , ", ", ' '");
                $str2 = array(" ", " ");
                $inputQuery = "select ".str_replace($str1, $str2, implode(", ", $aColumns))." from v_ssp_products where territory_id=f_get_cluster('".$userlogin."')";
                
                return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $status, $userlogin, $inputQuery, $aColumns, $sIndexColumn);
				// $query = $dblokal->query("select igrp.item_group_name, 
    //                                         itm.iccid,itm.mdn,imap.item_name, imap.item_code,itm.original_distributor_code,
    //                                         itm.original_ship_to_code,itm.shipment_date,itm.istatus from t_mtr_item itm 
    //                                         inner join t_mtr_item_mapping imap on itm.item_mapping_id=imap.item_mapping_id 
    //                                         inner join t_mtr_item_group igrp on igrp.item_group_id=imap.item_group_id  
    //                                         inner join t_mtr_distributor dist on dist.distributor_id=itm.distributor_id
    //                                         where dist.territory_id=f_get_cluster('".$this->session->userdata('username')."')");
    //             return $query->result();
		}
        
        function searchProduct(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select itm.iccid,itm.mdn,igrp.item_group_name, imap.item_name, dist.distributor_name,
                                            split_part(f_get_territory_path(dist.territory_id) , '>', 1) as region,
                                            split_part(f_get_territory_path(dist.territory_id) , '>',2) as cluster from t_mtr_item itm 
                                            inner join t_mtr_distributor dist on itm.distributor_id=dist.distributor_id inner join 
                                            t_mtr_item_mapping imap on imap.item_code=itm.item_code inner join 
                                            t_mtr_item_group igrp on imap.item_group_id=igrp.item_group_id
                                            where itm.iccid='".$this->input->post('param')."' or itm.mdn='".$this->input->post('param')."'");
                return $query->result_array();
        }

    public function search_product($keyword)
    {
        $this->db->select("ii.iccid, ii.mdn, ii.istatus, dd.distributor_name, mm.item_name, gg.item_group_name, ii.dwh_activated");
        $this->db->select("split_part(f_get_territory_path(dd.territory_id) , '>', 1) AS region", false);
        $this->db->select("split_part(f_get_territory_path(dd.territory_id) , '>',2) as cluster", false);
        $this->db->select("f_get_price_retail(ii.iccid) as price");
        $this->db->join("t_mtr_distributor AS dd","dd.distributor_id = ii.distributor_id");
        $this->db->join("t_mtr_item_mapping AS mm","mm.item_code = ii.item_code");
        $this->db->join("t_mtr_item_group AS gg","gg.item_group_id = ii.item_group_id");
        $this->db->where("ii.iccid", $keyword );
        $this->db->or_where("ii.mdn", $keyword );
        $query["item"] = $this->db->get("t_mtr_item AS ii")->row_array();

        if ($query["item"]) 
        {
                $query["status"] = $this->_item_status($query["item"]["iccid"]);
                $query["scan"] = $this->_item_scanned($query["item"]["iccid"]);
                $query["receipt"] = $this->_item_receipt($query["item"]["iccid"]);
                $query["sold"] = $this->_item_sold($query["item"]["iccid"]);
                $query["consignment"] = $this->_item_consignment($query["item"]["iccid"]);
            return $query;
        } 
        else 
            { return 0; }
    }

    private function _item_scanned($keyword)
    {
        $this->db->select('hh.scan_in_id, hh.created_on, hh.acess_via, dd.iccid, dd.istatus, uu.user_id, uu.user_name, hh.acess_via');
        $this->db->select("dd.transaction_type, dd.transaction_id");
        $this->db->join("t_trx_scan_in AS hh","hh.scan_in_id = dd.scan_in_id");
        $this->db->join("t_mtr_user AS uu","uu.user_id = hh.user_id", false);
        $this->db->like("dd.iccid", $keyword, 'after', false);
        $this->db->order_by('hh.scan_in_id', 'asc');
        $query = $this->db->get("t_trx_scan_in_detail AS dd");
        return $query->result_array();
    }
    private function _item_status($keyword)
    {
        $this->db->like("dd.iccid", $keyword, 'after', false);
        $this->db->order_by('dd.scan_in_id', 'desc');
        $query = $this->db->get("t_trx_scan_in_detail AS dd");
        return $query->row_array();
    }
    private function _item_receipt($keyword)
    {
        $this->db->select('hh.receipt_id, hh.created_on, uu.user_id, uu.user_name as created_by, hh.sales_id, xx.user_name');
        $this->db->join("t_trx_receipt AS hh","hh.receipt_id = dd.receipt_id");
        $this->db->join("t_mtr_user AS uu","uu.user_id = hh.created_by", false);
        $this->db->join("t_mtr_user AS xx","xx.user_id = hh.sales_id", false);
        $this->db->where("dd.iccid", $keyword);
        $query = $this->db->get("t_trx_receipt_detail AS dd");
        return $query->result_array();
    }

    private function _item_sold($keyword)
    {
        $this->db->select("hh.sell_in_id, hh.created_on, hh.channel_id, f_get_channel_name(hh.channel_id) AS channel_name, hh.user_id, uu.user_name, hh.acess_via",false);
        $this->db->join("t_trx_sell_in AS hh","hh.sell_in_id = dd.sell_in_id");
        $this->db->join("t_mtr_user AS uu","uu.user_id = hh.user_id");
        $this->db->where("dd.iccid", $keyword);
        $query = $this->db->get("t_trx_sell_in_detail AS dd");
        return $query->row_array();
    }

    private function _item_consignment($keyword)
    {
        $this->db->select("hh.consignment_id, hh.created_on, f_get_channel_id_by_territory_id(hh.territory_id) AS channel_id, dd.istatus");
        $this->db->select("f_get_channel_name(f_get_channel_id_by_territory_id(hh.territory_id)) AS channel_name, hh.user_id, uu.user_name, hh.acess_via");
        $this->db->join("t_trx_consignment AS hh","hh.consignment_id = dd.consignment_id");
        $this->db->join("t_mtr_user AS uu","uu.user_id = hh.user_id");
        $this->db->where("dd.iccid", $keyword);
        $query = $this->db->get("t_trx_consignment_detail AS dd");
        return $query->result_array();
    }

} //end class